package com.manager.base.impl;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.connection.ConnectionProvider;
import org.hibernate.engine.SessionFactoryImplementor;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.manager.base.BaseDao;
import com.manager.util.Result;

public class BaseDaoImpl extends HibernateDaoSupport implements BaseDao {
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * ----------------------------------JDBCTemplate方法----------------------------------
	 */
	public List queryList(String sql) {
		return this.jdbcTemplate.queryForList(sql);
	}
	
	public List queryList(String sql, Object... params){
		return this.jdbcTemplate.queryForList(sql, params);
	}
	
	public int queryInt(String sql){
		return this.jdbcTemplate.queryForInt(sql);
	}
	
	public int queryInt(String sql, Object... params){
		return this.jdbcTemplate.queryForInt(sql, params);
	}
	
	public void add(String sql){
		this.jdbcTemplate.update(sql);
	};
	
	public void add(String sql, Object... params){
		this.jdbcTemplate.update(sql, params);
	}
	
	public int add(PreparedStatementCreator psc, KeyHolder keyHolder){
		this.jdbcTemplate.update(psc, keyHolder);
		return keyHolder.getKey().intValue();
	}
	
	public void delete(String sql){
		this.jdbcTemplate.update(sql);
	};
	
	public void delete(String sql, Object... params){
		this.jdbcTemplate.update(sql, params);
	}
	
	public void update(String sql){
		this.jdbcTemplate.update(sql);
	}
	
	public void update(String sql, Object... params){
		this.jdbcTemplate.update(sql, params);
	}
	
	public int executeBatch(String[] sqls){
		return this.jdbcTemplate.batchUpdate(sqls)[0];
	}
	
	public int executeBatch(String sql, BatchPreparedStatementSetter pss){
		return this.jdbcTemplate.batchUpdate(sql, pss)[0];
	}
	
	public void execute(String sql){
		this.jdbcTemplate.execute(sql);
	};
	
	public List callProList(String proName, Object ... params) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		ResultSet resultSet = null;
		List list = null;
		StringBuffer sb = new StringBuffer();
		
		//组合Stored Procedure语句
		sb.append("{call ").append(proName).append(" (");
		if(params != null){
			for(int j = 0; j < params.length; j++){
				sb.append("?");
				sb.append(",");
			}
			sb.deleteCharAt(sb.length() - 1);
		}
		sb.append(")}");
		
		//执行Stored Procedure
		try {
			connection = this.jdbcTemplate.getDataSource().getConnection();
			callableStatement = connection.prepareCall(sb.toString());
			if(params != null){
				for(int i = 0; i < params.length; i++){
					callableStatement.setObject(i + 1, params[i]);
				}
			}
			resultSet = callableStatement.executeQuery();
			list = new Result(resultSet).getList();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(resultSet != null)resultSet.close();
				if(callableStatement != null)callableStatement.close();
				if(connection != null)connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}
	
	/**
	 * ----------------------------------HibernateTemplate方法----------------------------------
	 */
	public <T> T get(Class<T> entityClass,  Serializable id){
		return this.getHibernateTemplate().get(entityClass, id);
	}
	
	public <T> T get(Class<T> entityClass, Serializable id, LockMode lockMode){
		return this.getHibernateTemplate().get(entityClass, id, lockMode);
	}
	
	public List getList(String hql){
		return this.getHibernateTemplate().find(hql);
	}
	
	//eg: getObjectListByHT("from User user where u.name = ? and u.age =?","collonn",22)
	public List getList(String hql, Object... params){
		return this.getHibernateTemplate().find(hql, params);
	}
	
	public List getListTop(final String hql,final int length) {
		return getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException,SQLException{
				Query query = session.createQuery(hql);
				query.setFirstResult(0);
				query.setMaxResults(length);
				return query.list();
			}
		});
	}
	
	public List getListPage(final String hql,final int offset,final int length) {
		return getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException,SQLException{
				Query query = session.createQuery(hql);
				query.setFirstResult(offset);
				query.setMaxResults(length);
				return query.list();
			}
		});
	}
	
	//从hibernate的session中获得connection,执行sql后，返回一个组装的List<Map<String,Object>>
	public List getListBySql(final String sql) {
		return getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException,SQLException{
				Connection connection = null;
				Statement statement = null;
				ResultSet rs = null;
				List list = null;
				
				try{
					SessionFactoryImplementor sessionFactoryImplementor = (SessionFactoryImplementor)session.getSessionFactory();
					ConnectionProvider connectionProvider = sessionFactoryImplementor.getConnectionProvider();
					
					connection = connectionProvider.getConnection();
					statement = connection.createStatement();
					rs = statement.executeQuery(sql);
					list = new Result(rs).getList();
					
				}catch(SQLException e){
					e.printStackTrace();
				}finally{
					try{
						if(rs != null)rs.close();
						if(statement != null)statement.close();
						if(connection != null)connection.close();
					}catch(SQLException e){
						e.printStackTrace();
					}
				}
				
				return list;
			}
		});
	}
	
	public void save(Object entity){
		this.getHibernateTemplate().save(entity);
	}
	
	public void saveOrUpdateAll(Collection entities){
		this.getHibernateTemplate().saveOrUpdateAll(entities);
	}
	
	public void delete(Object entity){
		this.getHibernateTemplate().delete(entity);
	}
	
	public void delete(Class entityClass, Serializable id){
		Object obj = (Object)this.get(entityClass, id);
		this.getHibernateTemplate().delete(obj);
	}
	
	public void deleteAll(Collection entities){
		this.getHibernateTemplate().deleteAll(entities);
	}
	
	public void update(Object entity){
		this.getHibernateTemplate().update(entity);
	}
	
	/**
	 * getter and setter
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
